CREATE VIEW [dbo].[vGiftHistoryBase]
AS  SELECT  MAX(a.[ID]) ID,
           MAX(a.[ORIGINATING_TRANS_NUM]) OriginalTransaction,
           MAX(t.[INVOICE_REFERENCE_NUM]) AS InvoiceRefNum,
           MAX(a.[SOURCE_SYSTEM]) SourceSystem,
           MAX(a.[TRANSACTION_DATE]) TransactionDate,
           CASE
                 WHEN MAX(a.[SOURCE_SYSTEM]) = 'FR' THEN MAX(a.[EFFECTIVE_DATE])
                 ELSE MAX(a.[TRANSACTION_DATE]) END AS DateReceived,    
           ( SUM([t2].[AMOUNT]) * -1 ) AS Amount,
           MAX(a.[SOLICITOR_ID]) SolicitorID,
           ( CASE WHEN MAX(a.[ACTIVITY_TYPE]) = 'GIFT' THEN MAX(t.[CHECK_NUMBER])
                  ELSE ''
             END ) CheckNumber,
           MAX(a.[SOURCE_CODE]) Appeal,
           MAX(a.[CAMPAIGN_CODE]) Campaign,
           MAX(a.[ORG_CODE]) Fund,
           ( CASE WHEN MAX(a.[ACTIVITY_TYPE]) = 'GIFT'
                  THEN ( CASE MAX(c.[ACCOUNT_TYPE])
                           WHEN 1 THEN 'Credit Card'
                           WHEN 2 THEN 'In Kind'
                           WHEN 3 THEN 'Debit Card'
                           ELSE 'Cash'
                         END )
                  ELSE ''
             END ) AS PaymentType,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 5,
                                  2)) AS FiscalMonth,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 1,
                                  4)) AS FiscalYear,
           ( CASE WHEN MAX(a.[SOURCE_SYSTEM]) = 'DUES' THEN 'Membership'
                  WHEN MAX(a.[SOURCE_SYSTEM]) = 'MEETING' THEN 'Event'
                  WHEN MAX(a.[SOURCE_SYSTEM]) = 'FR'
                  THEN ( CASE WHEN MAX(a.[ACTIVITY_TYPE]) = 'PLEDGE'
                              THEN 'Pledge'
                              WHEN ( MAX(a.[ACTIVITY_TYPE]) = 'GIFT'
                                     AND MAX(c.[ACCOUNT_TYPE]) = 2
                                   ) THEN 'In Kind'
                              ELSE 'Cash'
                         END )
                  ELSE 'Gift'
             END ) AS GiftType,
           MAX(t.[MATCH_GIFT_TRANS_NUM]) MatchingTransaction,
           MAX(t.[IS_MATCH_GIFT]) IsMatchingGift,
           MAX(t.[MEM_TRIB_ID]) MemorialID,
           MAX(a.[ACTION_CODES]) ListAs,
           MAX(a.[UF_4]) RequestNumber,
           MAX(t.[MEM_TRIB_NAME_TEXT]) MemorialNameText,
           CAST(0.00 AS MONEY) AS SoftCreditAmount,
           '' AS SoftCreditDonorID
    FROM    [dbo].[Trans] t
           INNER JOIN [dbo].[Activity] a ON t.[ACTIVITY_SEQN] = a.[SEQN]
           LEFT OUTER JOIN [dbo].[Cash_Accounts] c ON t.[CHECK_NUMBER] = c.[CASH_ACCOUNT_CODE]
           INNER JOIN [dbo].[Invoice] i ON i.[REFERENCE_NUM] = t.[INVOICE_REFERENCE_NUM]
           INNER JOIN [dbo].[Trans] t2 ON i.[REFERENCE_NUM] = [t2].[INVOICE_REFERENCE_NUM]
    WHERE   [t2].[TRANSACTION_TYPE] = 'DIST'
           AND t.[JOURNAL_TYPE] = 'IN'
           AND t.[TRANSACTION_TYPE] = 'DIST'
           AND t.[PRODUCT_CODE] = [t2].[PRODUCT_CODE]
           AND ( i.[SOURCE_SYSTEM] = 'FR'
                 OR ( i.[SOURCE_SYSTEM] = 'DUES'
                      AND t.[INVOICE_LINE_NUM] = [t2].[INVOICE_LINE_NUM]
                    )
               )
           AND [t2].[IS_FR_ITEM] = 1
    GROUP BY a.[ID],
           a.[ORIGINATING_TRANS_NUM],
           a.[ORG_CODE],
           a.[CAMPAIGN_CODE],
           a.[SOURCE_CODE]
    UNION
    SELECT  MAX(a.[ID]) ID,
           MIN(i.[ORIGINATING_TRANS_NUM]) AS OriginalTransaction,
           MAX(t.[INVOICE_REFERENCE_NUM]) InvoiceRefNum,
           MAX(a.[SOURCE_SYSTEM]) SourceSystem,
           MAX(a.[TRANSACTION_DATE]) TransactionDate,
           CASE
                 WHEN MAX(a.[SOURCE_SYSTEM]) = 'FR' THEN MAX(a.[EFFECTIVE_DATE])
                 ELSE MAX(a.[TRANSACTION_DATE]) END AS DateReceived,
           SUM(a.[AMOUNT]) AS Amount,
           '' AS SolicitorID,
           '' AS CheckNumber,
           MAX(a.[SOURCE_CODE]) Appeal,
           MAX(a.[CAMPAIGN_CODE]) Campaign,
           MAX(a.[ORG_CODE]) Fund,
           '' AS PaymentType,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 5,
                                  2)) AS FiscalMonth,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 1,
                                  4)) AS FiscalYear,
           'Event' AS GiftType,
           0 AS MatchingTransaction,
           0 AS IsMatchingGift,
           '' AS MemorialID,
           '' AS ListAs,
           0 AS RequestNumber,
           '' AS MemorialNameText,
           CAST(0.00 AS MONEY) AS SoftCreditAmount,
           '' AS SoftCreditDonorID
    FROM    [dbo].[Activity] a
           INNER JOIN [dbo].[Trans] t ON a.[ORIGINATING_TRANS_NUM] = t.[TRANS_NUMBER]
           INNER JOIN [dbo].[Invoice] i ON i.[REFERENCE_NUM] = t.[INVOICE_REFERENCE_NUM]
    WHERE   a.[ACTIVITY_TYPE] = 'GIFT'
           AND a.[SOURCE_SYSTEM] = 'MEETING'
           AND t.[TRANSACTION_TYPE] = 'DIST'
           AND ( t.[PRODUCT_CODE] = a.[PRODUCT_CODE]
                 OR t.[PRODUCT_CODE] LIKE a.[PRODUCT_CODE] + '/%'
               )
    GROUP BY a.[ID],
          t.[INVOICE_REFERENCE_NUM],
           a.[ORG_CODE],
           a.[CAMPAIGN_CODE],
           a.[SOURCE_CODE]
    UNION
    SELECT  MAX(a.[ID]) ID,
           MAX(a.[ORIGINATING_TRANS_NUM]) AS OriginalTransaction,
           0 AS InvoiceRefNum,
           MAX(a.[SOURCE_SYSTEM]) SourceSystem,
           MAX(a.[TRANSACTION_DATE]) TransactionDate,
           CASE
                 WHEN MAX(a.[SOURCE_SYSTEM]) = 'FR' THEN MAX(a.[EFFECTIVE_DATE])
                 ELSE MAX(a.[TRANSACTION_DATE]) END AS DateReceived,
           SUM(a.[AMOUNT]) AS Amount,
           '' AS SolicitorID,
           MAX(t.[CHECK_NUMBER]) AS CheckNumber,
           MAX(a.[SOURCE_CODE]) Appeal,
           MAX(a.[CAMPAIGN_CODE]) Campaign,
           MAX(a.[ORG_CODE]) Fund,
           ( CASE MAX(c.[ACCOUNT_TYPE])
               WHEN 1 THEN 'Credit Card'
               WHEN 2 THEN 'In Kind'
               WHEN 3 THEN 'Debit Card'
               ELSE 'Cash'
             END ) AS PaymentType,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 5,
                                  2)) AS FiscalMonth,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 1,
                                  4)) AS FiscalYear,
           'Membership' AS GiftType,
           0 AS MatchingTransaction,
           0 AS IsMatchingGift,
           '' AS MemorialID,
           '' AS ListAs,
           0 AS RequestNumber,
           '' AS MemorialNameText,
           CAST(0.00 AS MONEY) AS SoftCreditAmount,
           '' AS SoftCreditDonorID
    FROM    [dbo].[Activity] a
           INNER JOIN [dbo].[Trans] t ON a.[ORIGINATING_TRANS_NUM] = t.[TRANS_NUMBER]
                                     AND t.[ST_ID] = a.[ID]
                                     AND t.[PRODUCT_CODE] = a.[PRODUCT_CODE]
           LEFT OUTER JOIN [dbo].[Cash_Accounts] c ON t.[CHECK_NUMBER] = c.[CASH_ACCOUNT_CODE]
    WHERE   a.[ACTIVITY_TYPE] = 'GIFT'
           AND a.[SOURCE_SYSTEM] IN ( 'DUES', 'SC' )
           AND t.[TRANSACTION_TYPE] = 'DIST'
    GROUP BY a.[ID],
           t.[TRANS_NUMBER],
           a.[ORG_CODE],
           a.[CAMPAIGN_CODE],
           a.[SOURCE_CODE]
    UNION
    SELECT  MAX(s.[SOFT_CREDIT_ID]) ID,
           MAX(a.[ORIGINATING_TRANS_NUM]) OriginalTransaction,
           MAX(t.[INVOICE_REFERENCE_NUM]) AS InvoiceRefNum,
           MAX(a.[SOURCE_SYSTEM]) SourceSystem,
           MAX(a.[TRANSACTION_DATE]) TransactionDate,
           MAX(a.[EFFECTIVE_DATE]) DateReceived,
           CAST(0.00 AS MONEY) AS Amount,
           MAX(a.[SOLICITOR_ID]) SolicitorID,
           ( CASE WHEN MAX(a.[ACTIVITY_TYPE]) = 'GIFT' THEN MAX(t.[CHECK_NUMBER])
                  ELSE ''
             END ) CheckNumber,
           MAX(a.[SOURCE_CODE]) Appeal,
           MAX(a.[CAMPAIGN_CODE]) Campaign,
           MAX(a.[ORG_CODE]) Fund,
           ( CASE WHEN MAX(a.[ACTIVITY_TYPE]) = 'GIFT'
                  THEN ( CASE MAX(c.[ACCOUNT_TYPE])
                           WHEN 1 THEN 'Credit Card'
                           WHEN 2 THEN 'In Kind'
                           WHEN 3 THEN 'Debit Card'
                           ELSE 'Cash'
                         END )
                  ELSE ''
             END ) AS PaymentType,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 5,
                                  2)) AS FiscalMonth,
           CONVERT(INT, SUBSTRING(CONVERT(CHAR(6), MAX(t.[FISCAL_PERIOD])), 1,
                                  4)) AS FiscalYear,
           'Soft Credit' AS GiftType,
           MAX(t.[MATCH_GIFT_TRANS_NUM]) MatchingTransaction,
           MAX(t.[IS_MATCH_GIFT]) IsMatchingGift,
           MAX(t.[MEM_TRIB_ID]) MemorialID,
           MAX(a.[ACTION_CODES]) ListAs,
           MAX(a.[UF_4]) RequestNumber,
           MAX(t.[MEM_TRIB_NAME_TEXT]) MemorialNameText,
           SUM(s.[AMOUNT]) AS SoftCreditAmount,
           MAX(a.[ID]) AS SoftCreditDonorID
    FROM    [dbo].[Trans] t
           INNER JOIN [dbo].[Activity] a ON t.[ACTIVITY_SEQN] = a.[SEQN]
           LEFT OUTER JOIN [dbo].[Cash_Accounts] c ON t.[CHECK_NUMBER] = c.[CASH_ACCOUNT_CODE]
           INNER JOIN [dbo].[Invoice] i ON i.[REFERENCE_NUM] = t.[INVOICE_REFERENCE_NUM]
           INNER JOIN [dbo].[Trans_SoftCredit] s ON s.[ORIGINATING_ACTIVITY_SEQN] = t.[ACTIVITY_SEQN]
    WHERE   t.[TRANSACTION_TYPE] = 'DIST'
           AND t.[JOURNAL_TYPE] = 'IN'
           AND t.[TRANSACTION_TYPE] = 'DIST'
           AND t.[PRODUCT_CODE] = s.[PRODUCT_CODE]
           AND i.[SOURCE_SYSTEM] = 'FR'
    GROUP BY a.[ID],
           a.[ORIGINATING_TRANS_NUM],
           s.[SOFT_CREDIT_ID],
           a.[ORG_CODE],
           a.[CAMPAIGN_CODE],
           a.[SOURCE_CODE]





GO